﻿using MySQLiteHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;

namespace SQLiteDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private SQLiteHelper SQLiteHelpers = null;
        private const string DBAddress = "D:\\SQLite\\Test.db";

        private void Form1_Load(object sender, EventArgs e)
        {
            SQLiteHelpers = new SQLiteHelper(DBAddress);
        }

        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_OpenDB_Click(object sender, EventArgs e)
        {
            SQLiteHelpers.Open();
            Label_DBOpenState.Text = "打开";
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_CloseDB_Click(object sender, EventArgs e)
        {
            SQLiteHelpers.Close();
            Label_DBOpenState.Text = "关闭";
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Query_Click(object sender, EventArgs e)
        {
            SQLiteParameter[] parameter = new SQLiteParameter[]
            {
                new SQLiteParameter("address", "济南")
            };
            string sql = "SELECT * FROM student WHERE address = @address";
            DataSet dataSet = SQLiteHelpers.ExecuteDataSet(sql, parameter);
            if (dataSet != null)
            {
                dataGridView1.DataSource = dataSet.Tables[0];
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Add_Click(object sender, EventArgs e)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic.Add("ID", 6);
            dic.Add("name", "王二麻子");
            dic.Add("age", 44);
            dic.Add("address", "陕西");

            int result = SQLiteHelpers.InsertData("student", dic);
            Console.WriteLine("插入结果，受影响的行数：" + result);
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Modify_Click(object sender, EventArgs e)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            //将列名 name 的值改为 “猴子”
            dic.Add("name", "猴子");
            //将列名 address 的值改为 花果山
            dic.Add("address", "花果山");
            //where条件
            string where = "ID = @ID AND age = @Age";
            //where条件中对应的参数
            SQLiteParameter[] parameter = new SQLiteParameter[]
            {
                new SQLiteParameter("ID", 4),
                new SQLiteParameter("Age",23)
            };
            
            int result = SQLiteHelpers.Update("student", dic, where, parameter);
            Console.WriteLine("修改结果，受影响的行数：" + result);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Delete_Click(object sender, EventArgs e)
        {
            //where条件
            string where = "ID = @ID";
            //where条件中对应的参数
            SQLiteParameter[] parameter = new SQLiteParameter[]
            {
                new SQLiteParameter("ID", 6),
            };

            int result = SQLiteHelpers.Delete("student", where, parameter);
            Console.WriteLine("删除结果，受影响的行数：" + result);
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_TableExists_Click(object sender, EventArgs e)
        {
            string title = "dddd";
            bool result = SQLiteHelpers.TableExists(title);
            Console.WriteLine(string.Format("{0} 表是否存在，结果：{1}", title, result));
        }

        //输出各表中的数据
        //public static void PrintValues(DataSet ds)
        //{
        //    foreach (DataTable table in ds.Tables)
        //    {
        //        Console.WriteLine("表名称：" + table.TableName);
        //        foreach (DataRow row in table.Rows)
        //        {
        //            foreach (DataColumn column in table.Columns)
        //            {
        //                Console.Write(row[column] + "");
        //            }
        //            Console.WriteLine();
        //        }
        //    }
        //}

    }
}
